Customer Case Study · Sales & Marketing · Business Intelligence
The oil and gas company's sales data sat in dozens of files, separate product and sales-group lists, and spreadsheets that each counted the year differently. HMC stitched it into one Power BI model with a fiscal-year calendar, DAX measures and a forecast that sales leaders could read and question.
Figures indicative; validated metrics available on request.
Every sales office sent its own file. Product names, sales groups and offices were kept in separate lists. Before anyone could ask "how are we tracking against last year?", someone had to spend days building the table to answer it.
Product, sales-code and numerous sales files sat in a folder, each in its own shape.
Without one model, insight was hard to get and decisions leaned on whichever spreadsheet was closest.
The oil and gas company's year runs April to March. Calendar-year dates in reports put quarters and weeks in the wrong buckets.
Copying, cleaning and recalculating consumed time and resources that should have gone into growth.
Built with the oil and gas company's sales analytics team, on Power BI and the files they already received.
Select a step to see what happened.
We listed every file, master and spreadsheet that fed sales reporting.
Deliverable: Source inventory and data dictionary.
One fact table, three dimensions, one direction of filtering.
Deliverable: Data model and measure list.
Three layers, each with one job.
Deliverable: Power BI model and Forecast dashboard.
New files go into the folder; the dashboard refreshes.
Deliverable: Refresh routine and user walkthrough.
The model became the base for more reports.
Deliverable: Reusable model template.
The original screens first, then three working replicas on synthetic data: the Power Query model build, the fiscal calendar, and the forecast studio.
Shown as delivered. Click any screen to enlarge it.
Press Refresh. Power Query reads every sales file in the folder, appends them, joins the product and sales-group masters, and loads the fact table. Click any table to peek at its rows.
Change the two inputs from the original DAX. The ring, the month grid and the calendar rows recompute with the same logic: fiscal month index, fiscal quarter, fiscal week and fiscal-year label.
A replica of the Forecast page on synthetic quarterly sales shaped like the original. Filter by product and region, change the horizon and confidence, hold back the last quarters to test accuracy, and drag the cut-off line.
Model: additive Holt-Winters (exponential smoothing with trend and seasonality), the same family Power BI's analytics-pane forecast uses.
Synthetic data for illustration. Shape follows the original chart (about ₹0.4M per quarter in 2019 rising to about ₹1.5M by late 2022).
The sales team stopped building the report and started reading it, with a forecast they could check against what actually happened.
Hours by task, before vs after
Mean absolute % error vs a 10% target
Figures indicative; validated metrics available on request.
A query that reads the whole folder never needs editing when next month's file arrives.
A parameterised fiscal calendar puts every week and quarter in the bucket the business actually uses.
A forecast with its uncertainty, back-tested against real quarters, earns trust that a single line never does.